
<!DOCTYPE html>
<html lang="zh-Hans" class="loading">
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
    <meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <title>Hibernate 自定义查询sql 并使用自定义对象接收查询结果 - Luis Blog</title>
    <meta name="apple-mobile-web-app-capable" content="yes" />
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <meta name="google" content="notranslate" />
    <meta name="keywords" content="Luis,"> 
    <meta name="description" content="java开发工程师，喜欢代码，喜欢技术,

在很多的生产中，hibernate并不能满足我们所有的开发需求，比如，很多表的联合查询，并且查询之后的各种结果封装在自定义的dto对象中，那么我们就需要使用自定义的sql进行查询了，好了，开始我,"> 
    <meta name="author" content="winter chen"> 
    <link rel="alternative" href="atom.xml" title="Luis Blog" type="application/atom+xml"> 
    <link rel="icon" href="/img/favicon.png"> 
    
    
<link rel="stylesheet" href="/css/diaspora.css">

	<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
    <script>
         (adsbygoogle = window.adsbygoogle || []).push({
              google_ad_client: "ca-pub-8691406134231910",
              enable_page_level_ads: true
         });
    </script>
    <script async custom-element="amp-auto-ads"
        src="https://cdn.ampproject.org/v0/amp-auto-ads-0.1.js">
    </script>
<meta name="generator" content="Hexo 4.2.0"></head>

<body class="loading">
    <span id="config-title" style="display:none">Luis Blog</span>
    <div id="loader"></div>
    <div id="single">
    <div id="top" style="display: block;">
    <div class="bar" style="width: 0;"></div>
    <a class="iconfont icon-home image-icon" href="javascript:;" data-url="https://blog.winterchen.com"></a>
    <div title="播放/暂停" class="iconfont icon-play"></div>
    <h3 class="subtitle">Hibernate 自定义查询sql 并使用自定义对象接收查询结果</h3>
    <div class="social">
        <div>
            <div class="share">
                <a title="获取二维码" class="iconfont icon-scan" href="javascript:;"></a>
            </div>
            <div id="qr"></div>
        </div>
    </div>
    <div class="scrollbar"></div>
</div>

    <div class="section">
        <div class="article">
    <div class='main'>
        <h1 class="title">Hibernate 自定义查询sql 并使用自定义对象接收查询结果</h1>
        <div class="stuff">
            <span>一月 18, 2018</span>
            
  <ul class="post-tags-list" itemprop="keywords"><li class="post-tags-list-item"><a class="post-tags-list-link" href="/tags/Hibernate/" rel="tag">Hibernate</a></li></ul>


        </div>
        <div class="content markdown">
            <p><img src="http://img.winterchen.com/alex-6723-unsplash.jpg" alt=""></p>
<blockquote>
<p>在很多的生产中，hibernate并不能满足我们所有的开发需求，比如，很多表的联合查询，并且查询之后的各种结果封装在自定义的dto对象中，那么我们就需要使用自定义的sql进行查询了，好了，开始我们新的旅程吧。</p>
</blockquote>
<a id="more"></a>
<h3 id="需求："><a href="#需求：" class="headerlink" title="需求："></a>需求：</h3><ul>
<li>n张表进行联合查询</li>
<li>将结果封装在一个DTO的对象中</li>
</ul>
<h3 id="代码："><a href="#代码：" class="headerlink" title="代码："></a>代码：</h3><p><strong>本文中使用了一个很复杂的联合查询的sql，所以大家并不需要了解详细，只需要只是我们进行一个很复杂的多张表进行联合查询的操作，最后将结果使用自定义的dto对象接收即可</strong></p>
<h4 id="SQL"><a href="#SQL" class="headerlink" title="SQL"></a>SQL</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	p9.projectId,</span><br><span class="line">	p9.fenxiaoMoney,</span><br><span class="line">	p9.shopCount</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	(</span><br><span class="line">		<span class="keyword">SELECT</span></span><br><span class="line">			p8.projectId,</span><br><span class="line">			p8.fenxiaoMoney,</span><br><span class="line">			<span class="keyword">count</span>(ic.inviteByPerson) shopCount</span><br><span class="line">		<span class="keyword">FROM</span></span><br><span class="line">		(</span><br><span class="line">			<span class="keyword">SELECT</span></span><br><span class="line">				p7.projectId,</span><br><span class="line">				p7.fenxiaoMoney</span><br><span class="line">			<span class="keyword">FROM</span></span><br><span class="line">				(</span><br><span class="line">					<span class="keyword">SELECT</span></span><br><span class="line">						pp.projectId,</span><br><span class="line">						g.fenxiaoMoney</span><br><span class="line">					<span class="keyword">FROM</span></span><br><span class="line">						(</span><br><span class="line">							<span class="keyword">SELECT</span></span><br><span class="line">								p6.projectId</span><br><span class="line">							<span class="keyword">FROM</span></span><br><span class="line">								(</span><br><span class="line">									<span class="keyword">SELECT</span></span><br><span class="line">										p5.projectId</span><br><span class="line">									<span class="keyword">FROM</span></span><br><span class="line">										(</span><br><span class="line">											<span class="keyword">SELECT</span></span><br><span class="line">												p4.projectId</span><br><span class="line">											<span class="keyword">FROM</span></span><br><span class="line">												(</span><br><span class="line">													<span class="keyword">SELECT</span></span><br><span class="line">														p2.projectId</span><br><span class="line">													<span class="keyword">FROM</span></span><br><span class="line">														(</span><br><span class="line">															<span class="keyword">SELECT</span></span><br><span class="line">																p1.projectId</span><br><span class="line">															<span class="keyword">FROM</span></span><br><span class="line">																(</span><br><span class="line">																	<span class="keyword">SELECT</span></span><br><span class="line">																		p.projectId</span><br><span class="line">																	<span class="keyword">FROM</span></span><br><span class="line">																		t_projects p</span><br><span class="line">																	<span class="keyword">WHERE</span></span><br><span class="line">																		<span class="number">1</span> = <span class="number">1</span></span><br><span class="line">																	<span class="keyword">AND</span> p.averagePrice &gt;= <span class="number">1.0</span></span><br><span class="line">																	<span class="keyword">AND</span> p.averagePrice &lt;= <span class="number">10000000000.0</span></span><br><span class="line">																	<span class="keyword">AND</span> p.saleLongitude &gt;= <span class="number">1.0</span></span><br><span class="line">																	<span class="keyword">AND</span> p.saleLongitude &lt;= <span class="number">10000.00</span></span><br><span class="line">																	<span class="keyword">AND</span> p.saleLatitude &gt;= <span class="number">1.0</span></span><br><span class="line">																	<span class="keyword">AND</span> p.saleLatitude &lt;= <span class="number">10000.0</span></span><br><span class="line">																	<span class="keyword">AND</span> p.rightsYears = <span class="number">70</span></span><br><span class="line">																	<span class="keyword">AND</span> p.city <span class="keyword">LIKE</span> <span class="string">'%330000%'</span> <span class="comment">#AND p.isOpenStatus = 1</span></span><br><span class="line">																	<span class="keyword">AND</span> p.buildArea &gt;= <span class="number">1.0</span></span><br><span class="line">																	<span class="keyword">AND</span> p.buildArea &lt;= <span class="number">10000000.0</span> <span class="comment">#AND p.afforestationRatio = 1</span></span><br><span class="line">																	<span class="keyword">AND</span> p.projectName <span class="keyword">LIKE</span> <span class="string">'%%'</span> <span class="comment">#AND p.projectId = '1515269bc87448b4927fa676c624c8f6'</span></span><br><span class="line">																) p1</span><br><span class="line">															<span class="keyword">INNER</span> <span class="keyword">JOIN</span> (</span><br><span class="line">																<span class="keyword">SELECT</span></span><br><span class="line">																	targetId</span><br><span class="line">																<span class="keyword">FROM</span></span><br><span class="line">																	t_tagsrelation</span><br><span class="line">																<span class="keyword">WHERE</span></span><br><span class="line">																	<span class="number">1</span> = <span class="number">1</span></span><br><span class="line">																<span class="keyword">AND</span> originalTags <span class="keyword">LIKE</span> <span class="string">'%4507%'</span></span><br><span class="line">																<span class="keyword">AND</span> originalTags <span class="keyword">LIKE</span> <span class="string">'%757%'</span></span><br><span class="line">															) t1 <span class="keyword">ON</span> p1.projectId = t1.targetId</span><br><span class="line">														) p2</span><br><span class="line">													<span class="keyword">INNER</span> <span class="keyword">JOIN</span> (</span><br><span class="line">														<span class="keyword">SELECT</span></span><br><span class="line">															h.projectId</span><br><span class="line">														<span class="keyword">FROM</span></span><br><span class="line">															t_projecthouses h</span><br><span class="line">														<span class="keyword">WHERE</span></span><br><span class="line">															h.houseNum <span class="keyword">IN</span> (</span><br><span class="line">																<span class="keyword">SELECT</span></span><br><span class="line">																	targetId</span><br><span class="line">																<span class="keyword">FROM</span></span><br><span class="line">																	t_tagsrelation</span><br><span class="line">																<span class="keyword">WHERE</span></span><br><span class="line">																	<span class="number">1</span> = <span class="number">1</span></span><br><span class="line">																<span class="keyword">AND</span> originalTags <span class="keyword">LIKE</span> <span class="string">'%%'</span></span><br><span class="line">															)</span><br><span class="line">														<span class="keyword">AND</span> h.houseStatus = <span class="number">1</span></span><br><span class="line">														<span class="keyword">AND</span> isOpen = <span class="number">1</span></span><br><span class="line">													) p3 <span class="keyword">ON</span> p2.projectId = p3.projectId</span><br><span class="line">												) p4</span><br><span class="line">											<span class="keyword">INNER</span> <span class="keyword">JOIN</span> (</span><br><span class="line">												<span class="keyword">SELECT</span></span><br><span class="line">													projectId</span><br><span class="line">												<span class="keyword">FROM</span></span><br><span class="line">													t_projecthousetypes pht</span><br><span class="line">												<span class="keyword">WHERE</span></span><br><span class="line">													<span class="number">1</span> = <span class="number">1</span></span><br><span class="line">												<span class="keyword">AND</span> pht.housType = <span class="string">'三房两厅一卫'</span></span><br><span class="line">											) pht1 <span class="keyword">ON</span> p4.projectId = pht1.projectId</span><br><span class="line">											<span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">												p4.projectId</span><br><span class="line">										) p5</span><br><span class="line">									<span class="keyword">INNER</span> <span class="keyword">JOIN</span> (</span><br><span class="line">										<span class="keyword">SELECT</span></span><br><span class="line">											ph.projectId</span><br><span class="line">										<span class="keyword">FROM</span></span><br><span class="line">											t_projecthouses ph</span><br><span class="line">										<span class="keyword">WHERE</span></span><br><span class="line">											<span class="number">1</span> = <span class="number">1</span></span><br><span class="line">										<span class="keyword">AND</span> (</span><br><span class="line">											ph.houseKind = <span class="string">'10'</span></span><br><span class="line">											<span class="keyword">OR</span> ph.houseKind = <span class="string">'0'</span></span><br><span class="line">										)</span><br><span class="line">									) hk <span class="keyword">ON</span> p5.projectId = hk.projectId</span><br><span class="line">									<span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">										p5.projectId</span><br><span class="line">								) p6</span><br><span class="line">							<span class="keyword">INNER</span> <span class="keyword">JOIN</span> (</span><br><span class="line">								<span class="keyword">SELECT</span></span><br><span class="line">									applyForPerson</span><br><span class="line">								<span class="keyword">FROM</span></span><br><span class="line">									t_applychart</span><br><span class="line">								<span class="keyword">WHERE</span></span><br><span class="line">									applyByPerson = <span class="string">'860635'</span></span><br><span class="line">								<span class="keyword">AND</span> applyStatus = <span class="number">2</span></span><br><span class="line">							) ap <span class="keyword">ON</span> p6.projectId = ap.applyForPerson</span><br><span class="line">						) pp</span><br><span class="line">					<span class="keyword">LEFT</span> <span class="keyword">JOIN</span> t_projectguide g <span class="keyword">ON</span> pp.projectId = g.projectId</span><br><span class="line">				) p7</span><br><span class="line">		) p8</span><br><span class="line">			</span><br><span class="line">		<span class="keyword">LEFT</span> <span class="keyword">JOIN</span> (<span class="keyword">SELECT</span> inviteByPerson <span class="keyword">FROM</span> t_invitechart <span class="keyword">WHERE</span> inviteStatus = <span class="number">2</span>) ic <span class="keyword">ON</span> p8.projectId = ic.inviteByPerson</span><br><span class="line">		<span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">			p8.projectId</span><br><span class="line">	) p9</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> (</span><br><span class="line">	<span class="keyword">SELECT</span></span><br><span class="line">		beCollectId</span><br><span class="line">	<span class="keyword">FROM</span></span><br><span class="line">		t_newcollectrecord</span><br><span class="line">	<span class="keyword">WHERE</span></span><br><span class="line">		userId = <span class="string">'4e0b0089-410a-497e-8056-6190e2a183d4'</span></span><br><span class="line">) nc <span class="keyword">ON</span> p9.projectId = nc.beCollectId</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">	p9.projectId</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span></span><br><span class="line">	shopCount <span class="keyword">DESC</span></span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">0</span>,</span><br><span class="line"> <span class="number">10</span></span><br></pre></td></tr></table></figure>

<p>从sql中可以看出我们需要查的三个参数：</p>
<ul>
<li>项目的Id</li>
<li>钱的数量</li>
<li>合作店铺的数量</li>
</ul>
<p>需要的额外功能：</p>
<ul>
<li>分页</li>
<li>根据上面的参数进行排序</li>
</ul>
<h4 id="SQL拼接方法"><a href="#SQL拼接方法" class="headerlink" title="SQL拼接方法"></a>SQL拼接方法</h4><p><strong>顾名思义就是将上述的sql使用java进行拼接，因为这样便于查看和bug查找(这段并不重要)</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br><span class="line">327</span><br><span class="line">328</span><br><span class="line">329</span><br><span class="line">330</span><br><span class="line">331</span><br><span class="line">332</span><br><span class="line">333</span><br><span class="line">334</span><br><span class="line">335</span><br><span class="line">336</span><br><span class="line">337</span><br><span class="line">338</span><br><span class="line">339</span><br><span class="line">340</span><br><span class="line">341</span><br><span class="line">342</span><br><span class="line">343</span><br><span class="line">344</span><br><span class="line">345</span><br><span class="line">346</span><br><span class="line">347</span><br><span class="line">348</span><br><span class="line">349</span><br><span class="line">350</span><br><span class="line">351</span><br><span class="line">352</span><br><span class="line">353</span><br><span class="line">354</span><br><span class="line">355</span><br><span class="line">356</span><br><span class="line">357</span><br><span class="line">358</span><br><span class="line">359</span><br><span class="line">360</span><br><span class="line">361</span><br><span class="line">362</span><br><span class="line">363</span><br><span class="line">364</span><br><span class="line">365</span><br><span class="line">366</span><br><span class="line">367</span><br><span class="line">368</span><br><span class="line">369</span><br><span class="line">370</span><br><span class="line">371</span><br><span class="line">372</span><br><span class="line">373</span><br><span class="line">374</span><br><span class="line">375</span><br><span class="line">376</span><br><span class="line">377</span><br><span class="line">378</span><br><span class="line">379</span><br><span class="line">380</span><br><span class="line">381</span><br><span class="line">382</span><br><span class="line">383</span><br><span class="line">384</span><br><span class="line">385</span><br><span class="line">386</span><br><span class="line">387</span><br><span class="line">388</span><br><span class="line">389</span><br><span class="line">390</span><br><span class="line">391</span><br><span class="line">392</span><br><span class="line">393</span><br><span class="line">394</span><br><span class="line">395</span><br><span class="line">396</span><br><span class="line">397</span><br><span class="line">398</span><br><span class="line">399</span><br><span class="line">400</span><br><span class="line">401</span><br><span class="line">402</span><br><span class="line">403</span><br><span class="line">404</span><br><span class="line">405</span><br><span class="line">406</span><br><span class="line">407</span><br><span class="line">408</span><br><span class="line">409</span><br><span class="line">410</span><br><span class="line">411</span><br><span class="line">412</span><br><span class="line">413</span><br><span class="line">414</span><br><span class="line">415</span><br><span class="line">416</span><br><span class="line">417</span><br><span class="line">418</span><br><span class="line">419</span><br><span class="line">420</span><br><span class="line">421</span><br><span class="line">422</span><br><span class="line">423</span><br><span class="line">424</span><br><span class="line">425</span><br><span class="line">426</span><br><span class="line">427</span><br><span class="line">428</span><br><span class="line">429</span><br><span class="line">430</span><br><span class="line">431</span><br><span class="line">432</span><br><span class="line">433</span><br><span class="line">434</span><br><span class="line">435</span><br><span class="line">436</span><br><span class="line">437</span><br><span class="line">438</span><br><span class="line">439</span><br><span class="line">440</span><br><span class="line">441</span><br><span class="line">442</span><br><span class="line">443</span><br><span class="line">444</span><br><span class="line">445</span><br><span class="line">446</span><br><span class="line">447</span><br><span class="line">448</span><br><span class="line">449</span><br><span class="line">450</span><br><span class="line">451</span><br><span class="line">452</span><br><span class="line">453</span><br><span class="line">454</span><br><span class="line">455</span><br><span class="line">456</span><br><span class="line">457</span><br><span class="line">458</span><br><span class="line">459</span><br><span class="line">460</span><br><span class="line">461</span><br><span class="line">462</span><br><span class="line">463</span><br><span class="line">464</span><br><span class="line">465</span><br><span class="line">466</span><br><span class="line">467</span><br><span class="line">468</span><br><span class="line">469</span><br><span class="line">470</span><br><span class="line">471</span><br><span class="line">472</span><br><span class="line">473</span><br><span class="line">474</span><br><span class="line">475</span><br><span class="line">476</span><br><span class="line">477</span><br><span class="line">478</span><br><span class="line">479</span><br><span class="line">480</span><br><span class="line">481</span><br><span class="line">482</span><br><span class="line">483</span><br><span class="line">484</span><br><span class="line">485</span><br><span class="line">486</span><br><span class="line">487</span><br><span class="line">488</span><br><span class="line">489</span><br><span class="line">490</span><br><span class="line">491</span><br><span class="line">492</span><br><span class="line">493</span><br><span class="line">494</span><br><span class="line">495</span><br><span class="line">496</span><br><span class="line">497</span><br><span class="line">498</span><br><span class="line">499</span><br><span class="line">500</span><br><span class="line">501</span><br><span class="line">502</span><br><span class="line">503</span><br><span class="line">504</span><br><span class="line">505</span><br><span class="line">506</span><br><span class="line">507</span><br><span class="line">508</span><br><span class="line">509</span><br><span class="line">510</span><br><span class="line">511</span><br><span class="line">512</span><br><span class="line">513</span><br><span class="line">514</span><br><span class="line">515</span><br><span class="line">516</span><br><span class="line">517</span><br><span class="line">518</span><br><span class="line">519</span><br><span class="line">520</span><br><span class="line">521</span><br><span class="line">522</span><br><span class="line">523</span><br><span class="line">524</span><br><span class="line">525</span><br><span class="line">526</span><br><span class="line">527</span><br><span class="line">528</span><br><span class="line">529</span><br><span class="line">530</span><br><span class="line">531</span><br><span class="line">532</span><br><span class="line">533</span><br><span class="line">534</span><br><span class="line">535</span><br><span class="line">536</span><br><span class="line">537</span><br><span class="line">538</span><br><span class="line">539</span><br><span class="line">540</span><br><span class="line">541</span><br><span class="line">542</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql中的p段</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p.projectId</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				t_projects p</span></span><br><span class="line"><span class="comment">			WHERE</span></span><br><span class="line"><span class="comment">				1 = 1</span></span><br><span class="line"><span class="comment">			AND p.averagePrice &gt;= 1.0</span></span><br><span class="line"><span class="comment">			AND p.averagePrice &lt;= 10000000000.0</span></span><br><span class="line"><span class="comment">			AND p.saleLongitude &gt;= 1.0</span></span><br><span class="line"><span class="comment">			AND p.saleLongitude &lt;= 10000.00</span></span><br><span class="line"><span class="comment">			AND p.saleLatitude &gt;= 1.0</span></span><br><span class="line"><span class="comment">			AND p.saleLatitude &lt;= 10000.0</span></span><br><span class="line"><span class="comment">			AND p.rightsYears = 70</span></span><br><span class="line"><span class="comment">			AND p.city LIKE '%330000%' #AND p.isOpenStatus = 1</span></span><br><span class="line"><span class="comment">			AND p.buildArea &gt;= 1.0</span></span><br><span class="line"><span class="comment">			AND p.buildArea &lt;= 10000000.0 #AND p.afforestationRatio = 1</span></span><br><span class="line"><span class="comment">			AND p.projectName LIKE '%%' #AND p.projectId = '1515269bc87448b4927fa676c624c8f6'</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p.projectId "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" t_projects p "</span>);</span><br><span class="line">		sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">		sb.append(<span class="string">" 1 = 1 "</span>);</span><br><span class="line">		<span class="keyword">if</span>(param.getMinAveragePrice() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.averagePrice &gt;= "</span> + param.getMinAveragePrice());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(param.getMaxAveragePrice() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.averagePrice &lt;= "</span> + param.getMaxAveragePrice());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getMinLongitudes()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.saleLongitude &gt;= "</span> + param.getMinLongitudes());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getMaxLongitudes()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.saleLongitude &lt;= "</span> + param.getMaxLongitudes());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getMinLatitudes()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.saleLatitude &gt;= "</span> + param.getMinLatitudes());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getMaxLatitudes()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.saleLatitude &lt;= "</span> + param.getMaxLatitudes());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(param.getRightsYears() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.rightsYears = "</span> + param.getRightsYears());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getCityId()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.city LIKE '%"</span> + param.getCityId() + <span class="string">"%'"</span>);</span><br><span class="line">		&#125;<span class="keyword">else</span> <span class="keyword">if</span>(!isEmpty(param.getCityName()))&#123;</span><br><span class="line">			String hql = <span class="string">"from CountryProvinceInfo where cityName = '"</span> + param.getCityName() + <span class="string">"' and cityLevel = '市' "</span>;</span><br><span class="line">			CountryProvinceInfo cp = (CountryProvinceInfo) baseDao.loadObject(hql);</span><br><span class="line">			String cityId = cp.getUpCityId();</span><br><span class="line">			sb.append(<span class="string">" AND p.city LIKE '%"</span> + cityId + <span class="string">"%'"</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="comment">//项目状态 在售、等待售、全部</span></span><br><span class="line">		<span class="keyword">if</span>(param.getProjectStatus() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			</span><br><span class="line">			sb.append(<span class="string">" AND p.isOpenStatus = "</span> + param.getProjectStatus());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(param.getMinBulidArea() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.buildArea &gt;= "</span> + param.getMinBulidArea());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(param.getMaxBuildArea() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.buildArea &lt;= "</span> + param.getMaxBuildArea());</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getProjectName()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.projectName LIKE '%"</span> + param.getProjectName() + <span class="string">"%' "</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getProjectId()))&#123;</span><br><span class="line">			sb.append(<span class="string">" AND p.projectId = '"</span> + param.getProjectId() + <span class="string">"' "</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的p1段 -- 项目标签过滤</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP1</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p1.projectId</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p&#125;//这个是p段</span></span><br><span class="line"><span class="comment">				) p1</span></span><br><span class="line"><span class="comment">			INNER JOIN (</span></span><br><span class="line"><span class="comment">				SELECT</span></span><br><span class="line"><span class="comment">					targetId</span></span><br><span class="line"><span class="comment">				FROM</span></span><br><span class="line"><span class="comment">					t_tagsrelation</span></span><br><span class="line"><span class="comment">				WHERE</span></span><br><span class="line"><span class="comment">					1 = 1</span></span><br><span class="line"><span class="comment">				AND originalTags LIKE '%4507%'</span></span><br><span class="line"><span class="comment">				AND originalTags LIKE '%757%'</span></span><br><span class="line"><span class="comment">			) t1 ON p1.projectId = t1.targetId</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p1.projectId "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP(param, user, sb);<span class="comment">//将p加入</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p1 "</span>);</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getpTags()))&#123;</span><br><span class="line">			sb.append(<span class="string">" INNER JOIN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" targetId "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_tagsrelation "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" 1 = 1 "</span>);</span><br><span class="line">			String[] pTags = <span class="keyword">this</span>.stringToArray(param.getpTags());</span><br><span class="line">			<span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number">0</span>; i &lt; pTags.length; i++) &#123;</span><br><span class="line">				sb.append(<span class="string">" AND originalTags LIKE '%"</span> + pTags[i] + <span class="string">"%' "</span>);</span><br><span class="line">			&#125;</span><br><span class="line">			sb.append(<span class="string">" ) t1 ON p1.projectId = t1.targetId "</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的p2段 -- 房源标签过滤</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP2</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p2.projectId</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p1&#125;//这个是p1段</span></span><br><span class="line"><span class="comment">				) p2</span></span><br><span class="line"><span class="comment">			INNER JOIN (</span></span><br><span class="line"><span class="comment">				SELECT</span></span><br><span class="line"><span class="comment">					h.projectId</span></span><br><span class="line"><span class="comment">				FROM</span></span><br><span class="line"><span class="comment">					t_projecthouses h</span></span><br><span class="line"><span class="comment">				WHERE</span></span><br><span class="line"><span class="comment">					h.houseNum IN (</span></span><br><span class="line"><span class="comment">						SELECT</span></span><br><span class="line"><span class="comment">							targetId</span></span><br><span class="line"><span class="comment">						FROM</span></span><br><span class="line"><span class="comment">							t_tagsrelation</span></span><br><span class="line"><span class="comment">						WHERE</span></span><br><span class="line"><span class="comment">							1 = 1</span></span><br><span class="line"><span class="comment">						AND originalTags LIKE '%%'</span></span><br><span class="line"><span class="comment">					)</span></span><br><span class="line"><span class="comment">				AND h.houseStatus = 1</span></span><br><span class="line"><span class="comment">				AND isOpen = 1</span></span><br><span class="line"><span class="comment">			) p3 ON p2.projectId = p3.projectId</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p2.projectId "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP1(param, user, sb);<span class="comment">//这个拼接p1</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p2 "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.gethTags()))&#123;</span><br><span class="line">			sb.append(<span class="string">" INNER JOIN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" h.projectId "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_projecthouses h "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" h.houseNum IN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" targetId "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_tagsrelation "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" 1=1 "</span>);</span><br><span class="line">			String[] hTags = <span class="keyword">this</span>.stringToArray(param.gethTags());</span><br><span class="line">			<span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number">0</span>; i &lt; hTags.length; i++) &#123;</span><br><span class="line">				sb.append(<span class="string">" AND originalTags LIKE '%"</span> + hTags[i] + <span class="string">"%' "</span>);</span><br><span class="line">			&#125;</span><br><span class="line">			sb.append(<span class="string">" ) "</span>);</span><br><span class="line">			sb.append(<span class="string">" AND h.houseStatus = 1 "</span>);</span><br><span class="line">			sb.append(<span class="string">" AND isOpen = 1 "</span>);</span><br><span class="line">			sb.append(<span class="string">" ) p3 ON p2.projectId = p3.projectId "</span>);</span><br><span class="line">			</span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的p4段 -- 房源户型</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP4</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p4.projectId</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p2&#125;//这个是p2段</span></span><br><span class="line"><span class="comment">				) p4</span></span><br><span class="line"><span class="comment">			INNER JOIN (</span></span><br><span class="line"><span class="comment">				SELECT</span></span><br><span class="line"><span class="comment">					projectId</span></span><br><span class="line"><span class="comment">				FROM</span></span><br><span class="line"><span class="comment">					t_projecthousetypes pht</span></span><br><span class="line"><span class="comment">				WHERE</span></span><br><span class="line"><span class="comment">					1 = 1</span></span><br><span class="line"><span class="comment">				AND pht.housType = '三房两厅一卫'</span></span><br><span class="line"><span class="comment">			) pht1 ON p4.projectId = pht1.projectId</span></span><br><span class="line"><span class="comment">			GROUP BY</span></span><br><span class="line"><span class="comment">				p4.projectId</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p4.projectId "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP2(param, user, sb);</span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p4 "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getHouseType()))&#123;</span><br><span class="line">			sb.append(<span class="string">" INNER JOIN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" projectId "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_projecthousetypes pht "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" 1 = 1 "</span>);</span><br><span class="line">			sb.append(<span class="string">" AND pht.housType = '"</span> + param.getHouseType() + <span class="string">"' "</span>);</span><br><span class="line">			sb.append(<span class="string">" ) pht1 ON p4.projectId = pht1.projectId "</span>);</span><br><span class="line">			sb.append(<span class="string">" GROUP BY "</span>);</span><br><span class="line">			sb.append(<span class="string">" p4.projectId "</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">		</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的p5段 -- 房源类型过滤</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP5</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p5.projectId</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p4&#125;//拼接p4段</span></span><br><span class="line"><span class="comment">				) p5</span></span><br><span class="line"><span class="comment">			INNER JOIN (</span></span><br><span class="line"><span class="comment">				SELECT</span></span><br><span class="line"><span class="comment">					ph.projectId</span></span><br><span class="line"><span class="comment">				FROM</span></span><br><span class="line"><span class="comment">					t_projecthouses ph</span></span><br><span class="line"><span class="comment">				WHERE</span></span><br><span class="line"><span class="comment">					1 = 1</span></span><br><span class="line"><span class="comment">				AND (</span></span><br><span class="line"><span class="comment">					ph.houseKind = '10'</span></span><br><span class="line"><span class="comment">					OR ph.houseKind = '0'</span></span><br><span class="line"><span class="comment">				)</span></span><br><span class="line"><span class="comment">			) hk ON p5.projectId = hk.projectId</span></span><br><span class="line"><span class="comment">			GROUP BY</span></span><br><span class="line"><span class="comment">				p5.projectId</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p5.projectId "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP4(param, user, sb);</span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p5 "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getHouseKinds()))&#123;</span><br><span class="line">			sb.append(<span class="string">" INNER JOIN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" ph.projectId "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_projecthouses ph "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" 1 = 1 "</span>);</span><br><span class="line">			sb.append(<span class="string">" AND ( "</span>);</span><br><span class="line">			String[] kinds = <span class="keyword">this</span>.stringToArray(param.getHouseKinds());</span><br><span class="line">			<span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number">0</span>; i &lt; kinds.length; i++) &#123;</span><br><span class="line">				<span class="keyword">if</span>(i == <span class="number">0</span>)&#123;</span><br><span class="line">					sb.append(<span class="string">" ph.houseKind = '"</span> + kinds[i] + <span class="string">"' "</span>);</span><br><span class="line">				&#125;<span class="keyword">else</span>&#123;</span><br><span class="line">					sb.append(<span class="string">" OR  ph.houseKind = '"</span> + kinds[i] + <span class="string">"' "</span>);</span><br><span class="line">				&#125;</span><br><span class="line">			&#125;</span><br><span class="line">			sb.append(<span class="string">" ) "</span>);</span><br><span class="line">			sb.append(<span class="string">" ) hk ON p5.projectId = hk.projectId "</span>);</span><br><span class="line">			sb.append(<span class="string">" GROUP BY p5.projectId "</span>);</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的p5段 -- 房源类型过滤</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP6</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p6.projectId</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p5&#125;//拼接p5段</span></span><br><span class="line"><span class="comment">				) p6</span></span><br><span class="line"><span class="comment">			INNER JOIN (</span></span><br><span class="line"><span class="comment">				SELECT</span></span><br><span class="line"><span class="comment">					applyForPerson</span></span><br><span class="line"><span class="comment">				FROM</span></span><br><span class="line"><span class="comment">					t_applychart</span></span><br><span class="line"><span class="comment">				WHERE</span></span><br><span class="line"><span class="comment">					applyByPerson = '860635'</span></span><br><span class="line"><span class="comment">				AND applyStatus = 2</span></span><br><span class="line"><span class="comment">			) ap ON p6.projectId = ap.applyForPerson</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p6.projectId "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP5(param, user, sb);</span><br><span class="line">		sb.append(<span class="string">" ) p6 "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">if</span>(param.getInviteStatus() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" INNER JOIN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" applyForPerson "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_applychart "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" applyByPerson = '"</span> + user.getParentId() + <span class="string">"' "</span>);</span><br><span class="line">			sb.append(<span class="string">" AND applyStatus = "</span> + param.getInviteStatus());</span><br><span class="line">			sb.append(<span class="string">" ) ap ON p6.projectId = ap.applyForPerson "</span>);</span><br><span class="line">			</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的PP段 -- 查看带看业务定义中的佣金</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlThePP</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				pp.projectId,</span></span><br><span class="line"><span class="comment">				g.fenxiaoMoney</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p6&#125;//拼接p6段</span></span><br><span class="line"><span class="comment">				) pp</span></span><br><span class="line"><span class="comment">			LEFT JOIN t_projectguide g ON pp.projectId = g.projectId</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" pp.projectId, "</span>);</span><br><span class="line">		sb.append(<span class="string">" g.fenxiaoMoney "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP6(param, user, sb);</span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) pp "</span>);</span><br><span class="line">		sb.append(<span class="string">" LEFT JOIN t_projectguide g ON pp.projectId = g.projectId "</span>);</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的P7段</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP7</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">				p7.projectId,</span></span><br><span class="line"><span class="comment">				p7.fenxiaoMoney</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;pp&#125;//拼接pp段</span></span><br><span class="line"><span class="comment">				) p7</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p7.projectId, "</span>);</span><br><span class="line">		sb.append(<span class="string">" p7.fenxiaoMoney "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlThePP(param, user, sb);</span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p7 "</span>);</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的P8段 -- 项目的合作门店数量</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP8</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb)</span></span>&#123;</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">			p8.projectId,</span></span><br><span class="line"><span class="comment">			p8.fenxiaoMoney,</span></span><br><span class="line"><span class="comment">			count(ic.inviteByPerson) shopCount</span></span><br><span class="line"><span class="comment">		FROM</span></span><br><span class="line"><span class="comment">		(</span></span><br><span class="line"><span class="comment">			&#123;p7&#125; //拼接P7段</span></span><br><span class="line"><span class="comment">		) p8</span></span><br><span class="line"><span class="comment">			</span></span><br><span class="line"><span class="comment">		LEFT JOIN t_invitechart ic ON p8.projectId = ic.inviteByPerson</span></span><br><span class="line"><span class="comment">		GROUP BY</span></span><br><span class="line"><span class="comment">			p8.projectId</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p8.projectId, "</span>);</span><br><span class="line">		sb.append(<span class="string">" p8.fenxiaoMoney, "</span>);</span><br><span class="line">		sb.append(<span class="string">" count(ic.inviteByPerson) shopCount "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP7(param, user, sb);</span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p8 "</span>);</span><br><span class="line">		sb.append(<span class="string">" LEFT JOIN (SELECT inviteByPerson FROM t_invitechart WHERE inviteStatus = 2) ic ON p8.projectId = ic.inviteByPerson "</span>);</span><br><span class="line">		sb.append(<span class="string">" GROUP BY p8.projectId "</span>);</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 拼接Hql的P9段 -- 个人是否收藏</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> param</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> user</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sb</span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">jointHqlTheP9</span><span class="params">(ShopSearchParam param, User user, StringBuilder sb, Page page)</span></span>&#123;</span><br><span class="line">		</span><br><span class="line">		<span class="comment">/**</span></span><br><span class="line"><span class="comment">		 * SELECT</span></span><br><span class="line"><span class="comment">		 	*</span></span><br><span class="line"><span class="comment">			FROM</span></span><br><span class="line"><span class="comment">				(</span></span><br><span class="line"><span class="comment">					&#123;p8&#125;//拼接p8段</span></span><br><span class="line"><span class="comment">				) p9</span></span><br><span class="line"><span class="comment">			LEFT JOIN (</span></span><br><span class="line"><span class="comment">				SELECT</span></span><br><span class="line"><span class="comment">					beCollectId</span></span><br><span class="line"><span class="comment">				FROM</span></span><br><span class="line"><span class="comment">					t_newcollectrecord</span></span><br><span class="line"><span class="comment">				WHERE</span></span><br><span class="line"><span class="comment">					userId = '4e0b0089-410a-497e-8056-6190e2a183d4'</span></span><br><span class="line"><span class="comment">			) nc ON p9.projectId = nc.beCollectId</span></span><br><span class="line"><span class="comment">			GROUP BY</span></span><br><span class="line"><span class="comment">				p9.projectId</span></span><br><span class="line"><span class="comment">			ORDER BY</span></span><br><span class="line"><span class="comment">				shopCount DESC</span></span><br><span class="line"><span class="comment">			LIMIT 0,</span></span><br><span class="line"><span class="comment">			 10</span></span><br><span class="line"><span class="comment">		 */</span></span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">		sb.append(<span class="string">" p9.projectId, p9.fenxiaoMoney, p9.shopCount "</span>);</span><br><span class="line">		sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">		sb.append(<span class="string">" ( "</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP8(param, user, sb);</span><br><span class="line">		</span><br><span class="line">		sb.append(<span class="string">" ) p9 "</span>);</span><br><span class="line">		<span class="keyword">if</span>(param.getIsClikeLike() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" LEFT JOIN ( "</span>);</span><br><span class="line">			sb.append(<span class="string">" SELECT "</span>);</span><br><span class="line">			sb.append(<span class="string">" beCollectId "</span>);</span><br><span class="line">			sb.append(<span class="string">" FROM "</span>);</span><br><span class="line">			sb.append(<span class="string">" t_newcollectrecord "</span>);</span><br><span class="line">			sb.append(<span class="string">" WHERE "</span>);</span><br><span class="line">			sb.append(<span class="string">" userId = '"</span> + user.getUserId() + <span class="string">"' "</span>);</span><br><span class="line">			sb.append(<span class="string">" ) nc ON p9.projectId "</span>);</span><br><span class="line">			<span class="keyword">if</span>(param.getIsClikeLike() == <span class="number">1</span>)&#123;<span class="comment">//收藏</span></span><br><span class="line">				sb.append(<span class="string">" = "</span>);</span><br><span class="line">			&#125;<span class="keyword">else</span>&#123;<span class="comment">//未收藏</span></span><br><span class="line">				sb.append(<span class="string">" != "</span>);</span><br><span class="line">			&#125;</span><br><span class="line">			sb.append(<span class="string">" nc.beCollectId "</span>);</span><br><span class="line">			</span><br><span class="line">			sb.append(<span class="string">" GROUP BY "</span>);</span><br><span class="line">			sb.append(<span class="string">" p9.projectId "</span>);</span><br><span class="line">		</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">if</span>(!isEmpty(param.getOrderType()) &amp;&amp; param.getOrder() != <span class="keyword">null</span>)&#123;</span><br><span class="line">			</span><br><span class="line">			sb.append(<span class="string">" ORDER BY "</span>);</span><br><span class="line">			<span class="keyword">if</span>(param.getOrderType().equals(ShopSearchConstant.ORDER_TYPE_SHOP_COUNT))&#123;<span class="comment">//合作门店数量</span></span><br><span class="line">				sb.append(<span class="string">" p9.shopCount "</span>);</span><br><span class="line">			&#125;<span class="keyword">else</span> <span class="keyword">if</span>(param.getOrderType().equals(ShopSearchConstant.ORDER_TYPE_MONEY))&#123;<span class="comment">//及时结款率  -  暂无</span></span><br><span class="line">				sb.append(<span class="string">" p9.shopCount "</span>);<span class="comment">//暂无，暂时用这个替代</span></span><br><span class="line">			&#125;<span class="keyword">else</span> <span class="keyword">if</span>(param.getOrderType().equals(ShopSearchConstant.ORDER_TYPE_COMMISSION))&#123;<span class="comment">//佣金高低</span></span><br><span class="line">				sb.append(<span class="string">" p9.fenxiaoMoney "</span>);</span><br><span class="line">			&#125;</span><br><span class="line">			sb.append(param.getOrderType());</span><br><span class="line">			<span class="keyword">if</span>(param.getOrder() == <span class="number">0</span>)&#123;</span><br><span class="line">				sb.append(<span class="string">" DESC "</span>);</span><br><span class="line">			&#125;<span class="keyword">else</span>&#123;</span><br><span class="line">				sb.append(<span class="string">" ASC "</span>);</span><br><span class="line">			&#125;</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">		<span class="keyword">if</span>(page != <span class="keyword">null</span>)&#123;</span><br><span class="line">			sb.append(<span class="string">" LIMIT "</span> + page.getStart() + <span class="string">","</span> + page.getLimit());</span><br><span class="line">		&#125;</span><br><span class="line">		</span><br><span class="line">		 </span><br><span class="line">	&#125;</span><br></pre></td></tr></table></figure>

<h3 id="Service-层"><a href="#Service-层" class="headerlink" title="Service 层"></a>Service 层</h3><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//这个方法是将反射之后得到的字段类型和字段名称进行拆分</span></span><br><span class="line"><span class="function"><span class="keyword">private</span> <span class="keyword">void</span> <span class="title">invokeDtoToFieldsToArray</span><span class="params">(Field[] fields, String[] colums, String[] types)</span></span>&#123;</span><br><span class="line">		<span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number">0</span>; i &lt; fields.length; i++) &#123;</span><br><span class="line">			types[i] = fields[i].getType().toString().replace(<span class="string">"class java.lang."</span>, <span class="string">""</span>);</span><br><span class="line">			colums[i] = fields[i].getName();</span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br></pre></td></tr></table></figure>


<p><strong>下面的方法就是整个Service中的核心方法了，将自定义sql交给hibernate进行处理，并且使用自定义的dto对象进行封装</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//这个方法就是将sql进行查询</span></span><br><span class="line"><span class="meta">@Override</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">findProjectListByShopSearchParam</span><span class="params">(ShopSearchParam shopSearchParam, Page page, User user)</span> </span>&#123;</span><br><span class="line">		StringBuilder sb = <span class="keyword">new</span> StringBuilder();</span><br><span class="line">		</span><br><span class="line">		<span class="comment">// 利用反射获取Project组装类的所有的字段和类型</span></span><br><span class="line">		Field[] fields = ProjectMapListDTO.class.getDeclaredFields();</span><br><span class="line">		String[] colums = <span class="keyword">new</span> String[fields.length];</span><br><span class="line">		String[] types = <span class="keyword">new</span> String[fields.length];</span><br><span class="line">		<span class="keyword">this</span>.invokeDtoToFieldsToArray(fields, colums, types);</span><br><span class="line">		<span class="keyword">this</span>.jointHqlTheP9(shopSearchParam, user, sb, page);</span><br><span class="line">		<span class="comment">//返回的结果</span></span><br><span class="line">		List&lt;ProjectMapTotalDTO&gt; rsList = <span class="keyword">new</span> LinkedList&lt;&gt;();</span><br><span class="line">		<span class="comment">//当前用户所有收藏的项目</span></span><br><span class="line">		List&lt;NewCollectRecord&gt; collList =  <span class="keyword">this</span>.findNewCollectRecordByUserId(user.getUserId(), ShopSearchConstant.COLLECT_TYPE_PROJECT,<span class="keyword">null</span>);</span><br><span class="line">		<span class="comment">//当前门店所有的申请</span></span><br><span class="line">		List&lt;ApplyChart&gt; appList = <span class="keyword">this</span>.findApplyChartsByShopId(user.getParentId(), <span class="keyword">null</span>);</span><br><span class="line">		<span class="comment">//---------重要---------这个就是关键方法</span></span><br><span class="line">		List&lt;ProjectMapListDTO&gt; list = baseDao.queryDTOBySql(sb.toString(), ProjectMapListDTO.class, colums, types);</span><br><span class="line">		</span><br><span class="line">		<span class="comment">//这里是工作需要，将查询出来的结果进行再次封装--看需要吧</span></span><br><span class="line">		<span class="comment">//需要进行判断是否收藏和申请状态的判断</span></span><br><span class="line">		<span class="keyword">for</span> (ProjectMapListDTO pd : list) &#123;</span><br><span class="line">			</span><br><span class="line">			ProjectMapTotalDTO ptd = <span class="keyword">new</span> ProjectMapTotalDTO();</span><br><span class="line">			Project project = (Project) baseDao.loadById(Project.class, pd.getProjectId());</span><br><span class="line">			ptd.setProject(project);</span><br><span class="line">			ptd.setFenxiaoMoney(pd.getFenxiaoMoney());</span><br><span class="line">			ptd.setShopCount(pd.getShopCount());</span><br><span class="line">			<span class="comment">//判断是否已经被收藏</span></span><br><span class="line">			<span class="keyword">boolean</span> flag = <span class="keyword">false</span>;</span><br><span class="line">			<span class="keyword">for</span>(NewCollectRecord nr : collList)&#123;</span><br><span class="line">				<span class="keyword">if</span>(nr.getBeCollectId().equals(pd.getProjectId()))&#123;</span><br><span class="line">					flag = <span class="keyword">true</span>;</span><br><span class="line">				&#125;</span><br><span class="line">			&#125;</span><br><span class="line">			</span><br><span class="line">			<span class="comment">//查看合作关系</span></span><br><span class="line">			<span class="keyword">for</span>(ApplyChart ac : appList)&#123;</span><br><span class="line">				<span class="keyword">if</span>(pd.getProjectId().equals(ac.getApplyForPerson()))&#123;</span><br><span class="line">					ptd.setApplyStatus(ac.getApplyStatus());</span><br><span class="line">				&#125;</span><br><span class="line">			&#125;</span><br><span class="line">			</span><br><span class="line">			ptd.setIsLike(flag ? <span class="number">1</span> : <span class="number">0</span>);</span><br><span class="line">			rsList.add(ptd);</span><br><span class="line">		&#125;</span><br><span class="line">		page.setRoot(rsList);</span><br><span class="line"></span><br><span class="line">		List&lt;ProjectMapTotalDTO&gt; lz = <span class="keyword">this</span>.findProjectListForMapNew(shopSearchParam, user);</span><br><span class="line">		page.setTotal(lz.size());</span><br><span class="line">		</span><br><span class="line">	&#125;</span><br></pre></td></tr></table></figure>

<p>看看DTO对象</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.sc.tradmaster.service.shop.impl.dto;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">ProjectMapListDTO</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">	<span class="keyword">private</span> String projectId;</span><br><span class="line">	<span class="keyword">private</span> Double fenxiaoMoney;</span><br><span class="line">	<span class="keyword">private</span> Integer shopCount;</span><br><span class="line">	</span><br><span class="line">	<span class="comment">//省略set和get方法</span></span><br><span class="line">	</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h4 id="DAO核心方法"><a href="#DAO核心方法" class="headerlink" title="DAO核心方法"></a>DAO核心方法</h4><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">BaseDaoImpl</span> <span class="keyword">extends</span> <span class="title">HibernateDaoSupport</span> <span class="keyword">implements</span> <span class="title">BaseDao</span></span>&#123;</span><br><span class="line"></span><br><span class="line">	<span class="meta">@Resource</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">mySessionFactory</span><span class="params">(SessionFactory sf)</span></span>&#123;</span><br><span class="line">		<span class="keyword">super</span>.setSessionFactory(sf);</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment">	 * 执行sql组装DTO对象集合</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> sql</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@param</span> clazz</span></span><br><span class="line"><span class="comment">	 * <span class="doctag">@return</span></span></span><br><span class="line"><span class="comment">	 */</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> List <span class="title">queryDTOBySql</span><span class="params">(String sql,Class clazz,String[] colums,String[] types)</span> </span>&#123;    </span><br><span class="line">		Session session = <span class="keyword">super</span>.getSessionFactory().getCurrentSession();</span><br><span class="line">		SQLQuery query = session.createSQLQuery(sql);</span><br><span class="line">		<span class="keyword">if</span>(colums!=<span class="keyword">null</span> &amp;&amp; types!=<span class="keyword">null</span> &amp;&amp; colums.length==types.length)&#123;</span><br><span class="line">			<span class="keyword">for</span>(<span class="keyword">int</span> i=<span class="number">0</span>;i&lt;colums.length;i++)&#123;</span><br><span class="line">				<span class="keyword">if</span>(types[i].equals(<span class="string">"Integer"</span>))&#123;</span><br><span class="line">					query.addScalar(colums[i],StandardBasicTypes.INTEGER);</span><br><span class="line">				&#125;<span class="keyword">else</span> <span class="keyword">if</span>(types[i].equals(<span class="string">"String"</span>))&#123;</span><br><span class="line">					query.addScalar(colums[i],StandardBasicTypes.STRING);</span><br><span class="line">				&#125;<span class="keyword">else</span> <span class="keyword">if</span>(types[i].equals(<span class="string">"Double"</span>))&#123;</span><br><span class="line">					query.addScalar(colums[i],StandardBasicTypes.DOUBLE);</span><br><span class="line">				&#125;<span class="keyword">else</span> <span class="keyword">if</span>(types[i].equals(<span class="string">"Long"</span>))&#123;</span><br><span class="line">					query.addScalar(colums[i],StandardBasicTypes.LONG);</span><br><span class="line">				&#125;</span><br><span class="line">				</span><br><span class="line">			&#125;</span><br><span class="line">		&#125;</span><br><span class="line">		List&lt;Object[]&gt; list = query.setResultTransformer(Transformers.aliasToBean(clazz)).list();</span><br><span class="line">        <span class="keyword">return</span> list;</span><br><span class="line">    &#125;</span><br><span class="line">   &#125;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结语：由于是在工作中用到的代码段，所以显得有些复杂化了，以后有时间的话，使用通俗易懂的方法讲解出来，先留个坑，如果大家遇到这种使用场景无法解决的话，欢迎打扰：<a href="mailto:1085143002@qq.com">1085143002@qq.com</a></p>
</blockquote>

            <!--[if lt IE 9]><script>document.createElement('audio');</script><![endif]-->
            <audio id="audio" loop="1" preload="auto" controls="controls" data-autoplay="false">
                <source type="audio/mpeg" src="">
            </audio>
            
                <ul id="audio-list" style="display:none">
                    
                        
                            <li title='0' data-url='https://raw.githubusercontent.com/WinterChenS/imgrpo/master/mp3/%E6%96%B0%E8%A3%A4%E5%AD%90-%E7%94%9F%E6%B4%BB%E5%9B%A0%E4%BD%A0%E8%80%8C%E7%81%AB%E7%83%AD.mp3'></li>
                        
                    
                </ul>
            
        </div>
        
    <div id='gitalk-container' class="comment link"
		data-enable='false'
        data-ae='false'
        data-ci='77f2adc33e0112d086d7'
        data-cs='e926aa3a621556609b2c2b76d89320fa524e23cd'
        data-r='WinterChenS.github.io'
        data-o='WinterChenS'
        data-a='WinterChenS'
        data-d='false'
    >查看评论</div>


    </div>
    
</div>


    </div>
</div>
</body>


<script src="//lib.baomitu.com/jquery/1.8.3/jquery.min.js"></script>
<script src="/js/plugin.js"></script>
<script src="/js/typed.js"></script>
<script src="/js/diaspora.js"></script>


<link rel="stylesheet" href="/photoswipe/photoswipe.css">
<link rel="stylesheet" href="/photoswipe/default-skin/default-skin.css">


<script src="/photoswipe/photoswipe.min.js"></script>
<script src="/photoswipe/photoswipe-ui-default.min.js"></script>


<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">
    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>
    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">
        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>
        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">
            <div class="pswp__top-bar">
                <!--  Controls are self-explanatory. Order can be changed. -->
                <div class="pswp__counter"></div>
                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>
                <button class="pswp__button pswp__button--share" title="Share"></button>
                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>
                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>
                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                      <div class="pswp__preloader__cut">
                        <div class="pswp__preloader__donut"></div>
                      </div>
                    </div>
                </div>
            </div>
            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div> 
            </div>
            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>
            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>
            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>
        </div>
    </div>
</div>





<!-- Google Analytics -->
<script type="text/javascript">
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');

  ga('create', 'G-30C04D6TMS', 'auto');
  ga('send', 'pageview');
</script>
<!-- End Google Analytics -->


</html>
